﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Linq;
using LdExpress;
using System.IO;
using System.Collections.Generic;
using System.Globalization;

public partial class _Default : System.Web.UI.Page
{
    public string ContainerPage = "./PricingOceanForSales.aspx";
    public string strCondition = "";
    public int NumOfItemsPerPage = 20;
    public string strPager = "";
    public string strError = "";
    public string strMess = "";
    public int nPermissions = 0;
    public int nPage = 1;
    public string strPageID = "page_number";
    public string strExcluded = "ID";
    public string strPartQuery = "";
    protected void Page_Load(object sender, EventArgs e)
    {

        int nPage = 1;
        try
        {

            if (Request.QueryString[strPageID] != null)
                nPage = int.Parse(Request.QueryString[strPageID]);
        }
        catch
        {
            nPage = 1;
        }
        if (nPage <= 0) nPage = 1;

        if ((Session["logined_id"] != null) && (int.Parse(Session["logined_id"].ToString()) > 0))
        {
            if (Session["PricingOceanForSales"] != "") nPermissions = int.Parse(Session["PricingOceanForSales"].ToString());
        }
        else
            Response.Redirect("Login.aspx");



        if (!Page.IsPostBack)
        {

            LoadSearch();
            LoadUserSearch();
            LoadLocationSearch();
            //Visibled Filter Option
            if (nPermissions >= 7)
            {
                ddRegionFromSearch.Visible = true;
                ddRegionToSearch.Visible = true;
                btnSearch.Visible = true;
            }
            if (nPermissions >= 9)
            {
                ddCarrSearch.Visible = true;
                ddPolSearch.Visible = true;
                ddPodSearch.Visible = true;
                ddFrmCountrySearch.Visible = true;
                ddToCountrySearch.Visible = true;
                ddSerAreaFromSearch.Visible = true;
                ddSerAreaToSearch.Visible = true;
                txtFrom.Visible = true;
                txtTo.Visible = true;

                ddEnquiry.Visible = true;
                ddLocationSearch.Visible = true;
            }
            if (nPermissions < 7) btnAddNew.Visible = false;
            if (Session["PricingOceanEnquiry"] != null)
                ddEnquiry.SelectedValue = Session["PricingOceanEnquiry"].ToString();

            CommonFunctions.GetAllQueryString(Request, "", "action;ID", out strPartQuery);
            if (Request.QueryString["action"] == "delete" && nPermissions > 7)
            {
                PricingOceanForSales obj = new PricingOceanForSales();
                obj.ID = long.Parse(Request.QueryString["ID"].ToString());
                obj.Delete(GV.cn, GV.trans);
                Response.Redirect("PricingOceanForSales.aspx" + strPartQuery);
            }

            #region Build List
            int nNumOfPages = 0;

            DataTable dtList;
            BuildList(strCondition, NumOfItemsPerPage, nPage, out nNumOfPages, out dtList, null, null);
            if (nPage > nNumOfPages) nPage = 1;

            rptData.DataSource = dtList;
            rptData.DataBind();
            #endregion
            #region Build Pager
            CommonFunctions.BuildPager(Request, nNumOfPages, nPage, strPageID, ContainerPage, out strPager);
            #endregion
        }

    }
    private void BuildList(string strCondition, int NumOfItemsPerPage, int PageNo, out int nNumOfPages, out DataTable dtList, MySqlConnection cn, MySqlTransaction trans)
    {
        DataSet ds = null;
        dtList = new DataTable();
        dtList.Columns.Add("Index");
        dtList.Columns.Add("ID");
        dtList.Columns.Add("PolName");
        dtList.Columns.Add("PodName");
        dtList.Columns.Add("ContRequest1");
        dtList.Columns.Add("ContRequest2");
        dtList.Columns.Add("UserRequest");
        dtList.Columns.Add("UpdateTimeRequest");
        dtList.Columns.Add("Targeted");
        dtList.Columns.Add("UserResponse");
        dtList.Columns.Add("DC20");
        dtList.Columns.Add("DC40");
        dtList.Columns.Add("HC40");
        dtList.Columns.Add("RF20");
        dtList.Columns.Add("RF40");
        dtList.Columns.Add("CarrName");
        dtList.Columns.Add("FRE");
        dtList.Columns.Add("TT_Response");
        dtList.Columns.Add("Suare");
        dtList.Columns.Add("Note_Response");
        dtList.Columns.Add("Bkg");
        dtList.Columns.Add("Idea");
        dtList.Columns.Add("Action");
        //Get List by SoIDs
        nNumOfPages = 0;
        string sqlCommand = "";
        string listIDs = "";

        if (Session["OceanCarrSearch"] != null && Session["OceanCarrSearch"].ToString() != "")
        {
            strCondition += " And CarrID=" + Session["OceanCarrSearch"].ToString();
        }
        if (Session["OceanFrmRegionSearch"] != null && Session["OceanFrmRegionSearch"].ToString() != "")
        {
            sqlCommand = "Select CountryCode From tbl_Countries Where ServiceAreaID in (Select ID from tbl_region where ID=" + Session["OceanFrmRegionSearch"].ToString() + ")";
            sqlCommand = "Select ID From  tbl_Ports where CountryCode in (" + sqlCommand + ")";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PolID in (" + listIDs + ")";
        }
        if (Session["OceanToRegionSearch"] != null && Session["OceanToRegionSearch"].ToString() != "")
        {
            sqlCommand = "Select CountryCode From tbl_Countries Where ServiceAreaID in (Select ID from tbl_region where ID=" + Session["OceanToRegionSearch"].ToString() + ")";
            sqlCommand = "Select ID From  tbl_Ports where CountryCode in (" + sqlCommand + ")";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PodID in (" + listIDs + ")";
        }
        if (Session["OceanFrmCountrySearch"] != null && Session["OceanFrmCountrySearch"].ToString() != "")
        {
            listIDs = "";
            sqlCommand = "Select ID From  tbl_Ports where CountryCode = '" + DBModule.RefineString(Session["OceanFrmCountrySearch"].ToString()) + "'";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PolID in (" + listIDs + ")";
        }

        if (Session["OceanToCountrySearch"] != null && Session["OceanToCountrySearch"].ToString() != "")
        {
            listIDs = "";
            sqlCommand = "Select ID From  tbl_Ports where CountryCode = '" + DBModule.RefineString(Session["OceanToCountrySearch"].ToString()) + "'";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PodID in (" + listIDs + ")";
        }
        if (Session["PricingOceanFrom"] != null && Session["PricingOceanFrom"].ToString() != "")
        {
            DateTime dt = DateTime.ParseExact(Session["PricingOceanFrom"].ToString(), "dd-MM-yyyy",
                                  CultureInfo.InvariantCulture);
            dt = new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0);
            strCondition += " AND UpdateTimeRequest>='" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        if (Session["PricingOceanTo"] != null && Session["PricingOceanTo"].ToString() != "")
        {
            DateTime dt = DateTime.ParseExact(Session["PricingOceanTo"].ToString(), "dd-MM-yyyy",
                                  CultureInfo.InvariantCulture);
            dt = new DateTime(dt.Year, dt.Month, dt.Day, 23, 59, 0);
            strCondition += " AND UpdateTimeRequest<='" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "'";
        }
        if (Session["PricingOceanLocation"] != null && Session["PricingOceanLocation"].ToString() != "")
        {
            sqlCommand = "SELECT ID FROM `tbl_adminusers` WHERE FIND_IN_SET('" + Session["PricingOceanLocation"].ToString() + "',VungIDs)";
            string userIds = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (userIds != null)
                strCondition += " And UserIDRequest in(" + userIds + ")";
        }
        if (Session["PricingOceanUser"] != null && Session["PricingOceanUser"].ToString() != "")
        {
            strCondition += " AND UserIDRequest =" + Session["PricingOceanUser"].ToString();
        }
        if (Session["OceanPolSearch"] != null && Session["OceanPolSearch"].ToString() != "")
        {
            strCondition += " AND POLID =" + Session["OceanPolSearch"].ToString();
        }
        if (Session["OceanPodSearch"] != null && Session["OceanPodSearch"].ToString() != "")
        {
            strCondition += " AND PODID =" + Session["OceanPodSearch"].ToString();
        }
        //Ser Area
        if (Session["OceanSerAreaFromSearch"] != null && Session["OceanSerAreaFromSearch"].ToString() != "")
        {
            sqlCommand = "Select ID From  tbl_Ports where ServiceAreaCode ='" + DBModule.RefineString(Session["OceanSerAreaFromSearch"].ToString()) + "'";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PolID in (" + listIDs + ")";
        }
        if (Session["OceanSerAreaToSearch"] != null && Session["OceanSerAreaToSearch"].ToString() != "")
        {
            sqlCommand = "Select ID From  tbl_Ports where ServiceAreaCode ='" + DBModule.RefineString(Session["OceanSerAreaToSearch"].ToString()) + "'";
            listIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
            if (listIDs == null) listIDs = "-1";
            if (listIDs.Length > 0) strCondition += " AND PodID in (" + listIDs + ")";
        }
        if (Session["PricingOceanEnquiry"] != null && int.Parse(Session["PricingOceanEnquiry"].ToString()) > 0)
        {
            int ndiffHour = int.Parse(Session["PricingOceanEnquiry"].ToString());
            if (ndiffHour == 8)
                strCondition += " HAVING KeyTTIsInput=0 AND DiffHours > " + 8;
            else if (ndiffHour == 4)
                strCondition += " HAVING (KeyTTIsInput=0 AND DiffHours > 4 AND DiffHours <=8)";
            else if (ndiffHour == 2)
                strCondition += " HAVING ( KeyTTIsInput=0 AND DiffHours > 2 AND DiffHours <=4)";
        }
        PricingOceanForSales.GetList(strCondition, "UpdateTimeRequest DESC", NumOfItemsPerPage, PageNo, out nNumOfPages, out ds, GV.cn, GV.trans);
        if (ds != null)
        {
            int count = 0;
            if (Request.QueryString["page_number"] != null && Request.QueryString["page_number"].ToString() != "")
            {
                int pagenumber = 1;
                try
                {
                    pagenumber = int.Parse(Request.QueryString["page_number"]);
                    count = NumOfItemsPerPage * (pagenumber - 1);
                }
                catch { count = 0; }
            }
            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                count++;
                string Index = count.ToString();
                string ID = dr["ID"].ToString();
                string PolName = dr["PolName"].ToString();
                string PodName = dr["PodName"].ToString();
                string ContRequest1 = dr["ContRequest1"].ToString();
                string ContRequest2 = dr["ContRequest2"].ToString();
                string UserRequest = dr["UserRequest"].ToString();
                string UpdateTimeRequest = DateTime.Parse(dr["UpdateTimeRequest"].ToString()).ToString("dd/MM/yyyy HH:mm");
                string UserResponse = dr["UserResponse"].ToString();
                string DC20 = dr["DC20"].ToString();
                if ((ContRequest1 == "DC20" || ContRequest2 == "DC20") && DC20 == "") DC20 += " X";
                string DC40 = dr["DC40"].ToString();
                if ((ContRequest1 == "DC40" || ContRequest2 == "DC40") && DC40 == "") DC40 += " X";
                string HC40 = dr["HC40"].ToString();
                if ((ContRequest1 == "HC40" || ContRequest2 == "HC40") && HC40 == "") HC40 += " X";
                string RF20 = dr["RF20"].ToString();
                if ((ContRequest1 == "RF20" || ContRequest2 == "RF20") && RF20 == "") RF20 += " X";
                string RF40 = dr["RF40"].ToString();
                if ((ContRequest1 == "RF40" || ContRequest2 == "RF40") && RF40 == "") RF40 += " X";
                string CarrName = dr["CarrName"].ToString();
                string FRE = dr["FRE"].ToString();
                string TT_Response = dr["TT_Response"].ToString();
                int nSuare = int.Parse(dr["Suare"].ToString());
                string strSuare = "<img src='Assets/images/star_0.gif'/><img src='Assets/images/star_0.gif'/><img src='Assets/images/star_0.gif'/>";
                if (nSuare == 1)
                    strSuare = "<img src='Assets/images/star_1.gif'/><img src='Assets/images/star_0.gif'/><img src='Assets/images/star_0.gif'/>";
                else if (nSuare == 2)
                    strSuare = "<img src='Assets/images/star_1.gif'/><img src='Assets/images/star_1.gif'/><img src='Assets/images/star_0.gif'/>";
                else if (nSuare == 3)
                    strSuare = "<img src='Assets/images/star_1.gif'/><img src='Assets/images/star_1.gif'/><img src='Assets/images/star_1.gif'/>";
                string Suare = strSuare;
                string Note_Response = dr["Note_Response"].ToString();
                string Bkg = dr["Bkg"].ToString();
                string Targeted = dr["Targeted"].ToString();
                string Idea = dr["Idea"].ToString();
                string Action = "";
                if (nPermissions >= 3)
                {
                    Action += " <a href=\"javascript:doEdit('" + ID.ToString() + "')\">" + "Edit</a>";
                    string DayEditable = GeneralSetting.GetValueByCode("PR_OCEAN_FOR_SALES_NOT_EDIT", GV.cn, GV.trans);
                    int nDayEditable = 7;
                    if (DayEditable != null)
                    {
                        try
                        {
                            nDayEditable = int.Parse(DayEditable);
                        }
                        catch { }
                    }
                    if (DateTime.Now <= DateTime.Parse(dr["UpdateTimeRequest"].ToString()).AddDays(nDayEditable))
                    {
                        Action += " - <a href=\"javascript:doRefer('" + ID.ToString() + "'," + dr["PolID"].ToString() + "," + dr["PoDID"].ToString() + ")\">" + "Rate</a>";
                        Action += " - <a href=\"Schedules.aspx?PortToID=" + dr["PolID"].ToString() + "&PortFromID=" + dr["PodID"].ToString() + "\"  target=\"_blank\" >Sche</a>";
                    }
                }
                if (nPermissions > 7)
                {
                    Action += " - <a href=\"javascript:doDelete('" + ID.ToString() + "')\">" + "Del</a>";
                }

                string[] strValues = new string[] {
                            Index,
                            ID , 
                            PolName,
                            PodName,
                            ContRequest1,
                            ContRequest2,
                            UserRequest,
                            UpdateTimeRequest,
                            Targeted,
                            UserResponse,
                            DC20,
                            DC40,
                            HC40,
                            RF20,
                            RF40,
                            CarrName,
                            FRE,
                            TT_Response,
                            Suare,
                            Note_Response,
                            Bkg,
                            Idea,
                            Action };
                dtList.Rows.Add(strValues);
            }
        }
    }
    protected void rptData_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        CheckBox cb = (CheckBox)e.Item.FindControl("cbBkg");
        HiddenField hidBkg = (HiddenField)e.Item.FindControl("hidBkg");
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            try
            {
                if (hidBkg.Value == "1")
                    cb.Checked = true;
                cb.Enabled = false;
            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }
        CheckBox cbTargeted = (CheckBox)e.Item.FindControl("cbTargeted");
        HiddenField hidTargeted = (HiddenField)e.Item.FindControl("hidTargeted");
        if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
        {
            try
            {
                if (hidTargeted.Value == "1")
                    cbTargeted.Checked = true;
                cbTargeted.Enabled = false;
            }
            catch (Exception ex)
            {
                Response.Write(ex.ToString());
            }
        }
    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {

        if (txtFrom.Text.ToLower() != "from")
            Session["PricingOceanFrom"] = txtFrom.Text;
        else
            Session["PricingOceanFrom"] = "";

        if (txtTo.Text.ToLower() != "to")
            Session["PricingOceanTo"] = txtTo.Text;
        else
            Session["PricingOceanTo"] = "";
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddCarrSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanCarrSearch"] = ddCarrSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddRegionFromSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanFrmRegionSearch"] = ddRegionFromSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddRegionToSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanToRegionSearch"] = ddRegionToSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddFrmCountrySearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanFrmCountrySearch"] = ddFrmCountrySearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddToCountrySearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanToCountrySearch"] = ddToCountrySearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddPolSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanPolSearch"] = ddPolSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddPodSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanPodSearch"] = ddPodSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddSerAreaFromSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanSerAreaFromSearch"] = ddSerAreaFromSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddSerAreaToSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["OceanSerAreaToSearch"] = ddSerAreaToSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    public void LoadSearch()
    {

        string sqlCommand = "SELECT DISTINCT PolID FROM tbl_pricingOceanForSales ";
        string lstPortIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
        if (lstPortIDs == null) lstPortIDs = "-1";
        DataSet ds;
        //POL
        sqlCommand = "SELECT ID,Name From tbl_Ports where ID in(" + lstPortIDs + ") ORDER BY Name";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddPolSearch.DataSource = ds.Tables[0];
        ddPolSearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddPolSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddPolSearch.DataBind();
        ddPolSearch.Items.Insert(0, new ListItem("-- POL --", ""));
        if (Session["OceanPolSearch"] != null)
        {
            if (ddPolSearch.Items.FindByValue(Session["OceanPolSearch"].ToString()) != null)
                ddPolSearch.SelectedValue = Session["OceanPolSearch"].ToString();
        }
        //Frm SerArea
        sqlCommand = "SELECT Code From tbl_ServiceAreas where Code in (SELECT ServiceAreaCode FROM tbl_ports WHERE id IN(" + lstPortIDs + ")) order by Code";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddSerAreaFromSearch.DataSource = ds.Tables[0];
        ddSerAreaFromSearch.DataTextField = ds.Tables[0].Columns[0].ToString();
        ddSerAreaFromSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddSerAreaFromSearch.DataBind();
        ddSerAreaFromSearch.Items.Insert(0, new ListItem("-- Frm SerArea --", ""));
        if (Session["OceanSerAreaFromSearch"] != null)
        {
            if (ddSerAreaFromSearch.Items.FindByValue(Session["OceanSerAreaFromSearch"].ToString()) != null)
                ddSerAreaFromSearch.SelectedValue = Session["OceanSerAreaFromSearch"].ToString();
        }
        //FRM Country
        sqlCommand = "SELECT CountryCode,CountryName FROM tbl_countries WHERE countryCode IN " +
                     " (SELECT CountryCode FROM tbl_ports WHERE id IN(" + lstPortIDs + ")) order by CountryName";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddFrmCountrySearch.DataSource = ds.Tables[0];
        ddFrmCountrySearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddFrmCountrySearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddFrmCountrySearch.DataBind();
        ddFrmCountrySearch.Items.Insert(0, new ListItem("-- Frm Country --", ""));
        if (Session["OceanFrmCountrySearch"] != null)
        {
            if (ddFrmCountrySearch.Items.FindByValue(Session["OceanFrmCountrySearch"].ToString()) != null)
                ddFrmCountrySearch.SelectedValue = Session["OceanFrmCountrySearch"].ToString();
        }
        //Frm Region
        sqlCommand = "SELECT ID,Name From tbl_Region where  " +
                   " ID in (SELECT DISTINCT ServiceAreaID FROM tbl_countries WHERE countryCode IN " +
                    " (SELECT CountryCode FROM tbl_ports WHERE id IN(" + lstPortIDs + "))) order by Name";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddRegionFromSearch.DataSource = ds.Tables[0];
        ddRegionFromSearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddRegionFromSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddRegionFromSearch.DataBind();
        ddRegionFromSearch.Items.Insert(0, new ListItem("-- Frm Region --", ""));
        if (Session["OceanFrmRegionSearch"] != null)
        {
            if (ddRegionFromSearch.Items.FindByValue(Session["OceanFrmRegionSearch"].ToString()) != null)
                ddRegionFromSearch.SelectedValue = Session["OceanFrmRegionSearch"].ToString();
        }

        sqlCommand = "SELECT DISTINCT PodID FROM tbl_pricingOceanForSales ";
        lstPortIDs = DBModule.ExecuteQueryForListValue(sqlCommand, GV.cn, GV.trans);
        if (lstPortIDs == null) lstPortIDs = "-1";
        //POD
        sqlCommand = "SELECT ID,Name From tbl_Ports where ID in(" + lstPortIDs + ") ORDER BY Name";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddPodSearch.DataSource = ds.Tables[0];
        ddPodSearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddPodSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddPodSearch.DataBind();
        ddPodSearch.Items.Insert(0, new ListItem("-- POD --", ""));
        if (Session["OceanPodSearch"] != null)
        {
            if (ddPodSearch.Items.FindByValue(Session["OceanPodSearch"].ToString()) != null)
                ddPodSearch.SelectedValue = Session["OceanPodSearch"].ToString();
        }

        //To SerArea
        sqlCommand = "SELECT Code From tbl_ServiceAreas where Code in (SELECT ServiceAreaCode FROM tbl_ports WHERE id IN(" + lstPortIDs + ")) order by Code";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddSerAreaToSearch.DataSource = ds.Tables[0];
        ddSerAreaToSearch.DataTextField = ds.Tables[0].Columns[0].ToString();
        ddSerAreaToSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddSerAreaToSearch.DataBind();
        ddSerAreaToSearch.Items.Insert(0, new ListItem("-- To SerArea --", ""));
        if (Session["OceanSerAreaToSearch"] != null)
        {
            if (ddSerAreaToSearch.Items.FindByValue(Session["OceanSerAreaToSearch"].ToString()) != null)
                ddSerAreaToSearch.SelectedValue = Session["OceanSerAreaToSearch"].ToString();
        }
        //To Country
        sqlCommand = "SELECT CountryCode,CountryName FROM tbl_countries WHERE countryCode IN " +
                     " (SELECT CountryCode FROM tbl_ports WHERE id IN(" + lstPortIDs + ")) order by CountryName";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddToCountrySearch.DataSource = ds.Tables[0];
        ddToCountrySearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddToCountrySearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddToCountrySearch.DataBind();
        ddToCountrySearch.Items.Insert(0, new ListItem("-- To Country --", ""));
        if (Session["OceanToCountrySearch"] != null)
        {
            if (ddToCountrySearch.Items.FindByValue(Session["OceanToCountrySearch"].ToString()) != null)
                ddToCountrySearch.SelectedValue = Session["OceanToCountrySearch"].ToString();
        }
        //To Region
        sqlCommand = "SELECT ID,Name From tbl_Region where  " +
                   " ID in (SELECT DISTINCT ServiceAreaID FROM tbl_countries WHERE countryCode IN " +
                    " (SELECT CountryCode FROM tbl_ports WHERE id IN(" + lstPortIDs + "))) order by Name";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddRegionToSearch.DataSource = ds.Tables[0];
        ddRegionToSearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddRegionToSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddRegionToSearch.DataBind();
        ddRegionToSearch.Items.Insert(0, new ListItem("-- To Region --", ""));
        if (Session["OceanToRegionSearch"] != null)
        {
            if (ddRegionToSearch.Items.FindByValue(Session["OceanToRegionSearch"].ToString()) != null)
                ddRegionToSearch.SelectedValue = Session["OceanToRegionSearch"].ToString();
        }

        //Carr
        sqlCommand = "SELECT ID,Name From tbl_Carrs WHERE ID in (SELECT DISTINCT CarrID FROM tbl_pricingOceanForSales) ORDER BY Name";
        ds = DBModule.ExecuteQuery(sqlCommand, GV.cn, GV.trans);
        ddCarrSearch.DataSource = ds.Tables[0];
        ddCarrSearch.DataTextField = ds.Tables[0].Columns[1].ToString();
        ddCarrSearch.DataValueField = ds.Tables[0].Columns[0].ToString();
        ddCarrSearch.DataBind();
        ddCarrSearch.Items.Insert(0, new ListItem("-- Carr --", ""));
        if (Session["OceanCarrSearch"] != null)
        {
            if (ddCarrSearch.Items.FindByValue(Session["OceanCarrSearch"].ToString()) != null)
                ddCarrSearch.SelectedValue = Session["OceanCarrSearch"].ToString();
        }


        //Date Time
        //if (Session["PricingOceanFrom"] == null)
        //    Session["PricingOceanFrom"] = DateTime.Now.AddDays(-14).ToString("dd-MM-yyyy");
        //if (Session["PricingOceanTo"] == null)
        //    Session["PricingOceanTo"] = DateTime.Now.ToString("dd-MM-yyyy");
        if (Session["PricingOceanFrom"] != null)
            txtFrom.Text = Session["PricingOceanFrom"].ToString();
        if (Session["PricingOceanTo"] != null)
            txtTo.Text = Session["PricingOceanTo"].ToString();
    }
    protected void LoadLocationSearch()
    {

        DataSet ds;
        Location.DoSearch("", "Name", out ds, GV.cn, GV.trans);
        DataTable dt = ds.Tables[0];
        //Location Search
        ddLocationSearch.Items.Add((new ListItem("-- Location --", "")));
        ddLocationSearch.AppendDataBoundItems = true;
        ddLocationSearch.DataSource = dt;
        ddLocationSearch.DataTextField = dt.Columns["Name"].ToString();
        ddLocationSearch.DataValueField = dt.Columns["ID"].ToString();
        ddLocationSearch.DataBind();
        if (Session["PricingOceanLocation"] != null)
        {
            if (ddLocationSearch.Items.FindByValue(Session["PricingOceanLocation"].ToString()) != null)
                ddLocationSearch.SelectedValue = Session["PricingOceanLocation"].ToString();
        }
    }
    protected void ddLocationSearch_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PricingOceanLocation"] = ddLocationSearch.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void LoadUserSearch()
    {

        DataSet ds;
        string SqlCommand = "Select ID,UserName From tbl_AdminUsers where ID in(SELECT DISTINCT UserIDRequest FROM tbl_pricingoceanforsales)";
        if (nPermissions < 9)
            SqlCommand += " AND ID=" + Session["logined_id"].ToString();
        ds = DBModule.ExecuteQuery(SqlCommand, GV.cn, GV.trans);
        DataTable dt = ds.Tables[0];
        //Location Search
        ddUser.Items.Add((new ListItem("-- User --", "")));
        ddUser.AppendDataBoundItems = true;
        ddUser.DataSource = dt;
        ddUser.DataTextField = dt.Columns["UserName"].ToString();
        ddUser.DataValueField = dt.Columns["ID"].ToString();
        ddUser.DataBind();
        if (Session["PricingOceanUser"] != null)
        {
            if (ddUser.Items.FindByValue(Session["PricingOceanUser"].ToString()) != null)
                ddUser.SelectedValue = Session["PricingOceanUser"].ToString();
        }
    }
    protected void ddUser_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PricingOceanUser"] = ddUser.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    protected void ddEnquiry_SelectedIndexChanged(object sender, EventArgs e)
    {
        Session["PricingOceanEnquiry"] = ddEnquiry.SelectedValue;
        Response.Redirect("PricingOceanForSales.aspx");
    }
    public string GetNoticeClass(object TimeRequest, object oDC20, object oDC40, object oHC40, object oRF20, object oRF40)
    {
        string result = "";
        if (TimeRequest != null)
        {
            string DC20 = oDC20.ToString().Trim() == "X" ? "" : oDC20.ToString();
            string DC40 = oDC40.ToString().Trim() == "X" ? "" : oDC40.ToString();
            string HC40 = oHC40.ToString().Trim() == "X" ? "" : oHC40.ToString();
            string RF20 = oRF20.ToString().Trim() == "X" ? "" : oRF20.ToString();
            string RF40 = oRF40.ToString().Trim() == "X" ? "" : oRF40.ToString();
            if (DC20.ToString() == "" && DC40.ToString() == "" && HC40.ToString() == "" && RF20.ToString() == "" && RF40.ToString() == "")
            {
                IFormatProvider culture = new CultureInfo("vi-VN", true);
                DateTime dt = DateTime.Parse(TimeRequest.ToString(), culture);
                DateTime currTime = DateTime.Now;
                if (currTime >= dt.AddHours(8))// Mau do
                    result = "notice8hour";
                else if (currTime >= dt.AddHours(4))// Mau tim
                    result = "notice4hour";
                else if (currTime >= dt.AddHours(2))// Mau cam
                    result = "notice2hour";
            }
        }
        return result;
    }
}
